# Responsability
# Initialize and insert data in sqlite database
require("RSQLite")


Database <- setRefClass(    
  "database"
  , fields = list(
     path="character",
     file="character",
     conn="SQLiteConnection"
  )
  , methods = list(
    #
    #
    # Constructor
    #
    #
    initialize = function(...,
                          path = configuration@commonFolder,
                          file = configuration@dbFile)
    {
      
      print("Initialize Database")
      
      callSuper(...,
                path = path,
                file = file)
      
      conn  <<- dbConnect(dbDriver("SQLite"), dbname = paste(path,file,sep=""))
      
    },
    #
    #
    # Create tables if they do not exist 
    # colnames: a vector with the parameter names
    #
    create = function(colnames) {
      tryCatch({
        
        dbSendQuery(conn, paste("CREATE TABLE Parameters(chain INTEGER, iteration INTEGER, likelihood FLOAT",
                                getColNamesCreateQuery(colnames),");", sep=""))
        dbSendQuery(conn, "CREATE TABLE Likelyhood (chain INTEGER, iteration INTEGER, likelihood FLOAT);")
        
      },
      error= function(err) {
           print(err)
           print("Error creating tables. They probably exists")
      })
      
    },
    #
    #
    # Prepare the column names query
    #
    #
    getColNamesCreateQuery = function(colnames) {
      
      query <- ""
      
      for (i in 1:length(colnames)) {
        query <- paste(query,", ",colnames[i]," FLOAT",sep="")
      }
      
      query
    },
    #
    #
    # Close database connection
    #
    #
    close =  function() {
      tryCatch({
        
        dbDisconnect(conn)
        
      },
      error= function(err) {
        show(err)
        print("Error closing database")
      })
    },
    #
    #
    # Insert parameters
    # Parameters: data.frame, rownames has the name of the parameters and parameters[,1] are the values
    #
    insertParameters = function(chain, iteration, likelihood, parameters) {
      
      sqlQuery(paste("INSERT INTO Parameters (chain, iteration, likelihood",
                     getColNamesInsertQuery(rownames(parameters)),") VALUES (", chain, ",", iteration, ",", likelihood,
                     getColValuesInsertQuery(parameters[,1]),")", sep=""))
      
    },
    getColNamesInsertQuery = function(colnames) {
  
      query <- ""
  
      for (i in 1:length(colnames)) {
        query <- paste(query,", ",colnames[i],sep="")
      }
      
      query
    },
    getColValuesInsertQuery = function(values) {
      
      query <- ""
      
      for (i in 1:length(values)) {
        query <- paste(query,", ",values[i],sep="")
      }
      
      query
    },
    #
    #
    # Secure function to send sqlQuerys with error handling
    #
    #
    sqlQuery = function(query,...)
    {
      print(paste("Executing query: ", query))
      
      tryCatch(
        
        return(dbSendQuery(conn, query))
        , error=function(e) { 
          print(paste("Caught RSQLite Error: dbSendQuery() Failed with query", query))
          stop(e)
        })
    },
    #
    #
    # insert VALUES into table
    #
    # 
    insert = function(tabname, values)
    {
      sqlQuery(paste("INSERT INTO ",tabname," VALUES ", values, sep="") )
    },
    
    # to put invert commas (apostrophs) around strings for use in db value inserts
    ticks = function(X,mark="'")
    {
      paste(mark, X ,mark,sep="")
    } 
    
    
    
    
    
    
  )#End methods List 
  
)#End RefClass






#------------------
# 'generic' functions to save Values of parameters, likelihood, ... 
#------------------


# function to create VALUE Pairs according to DB tables for insertion
# DB_insert.parameter <- function(chain,iteration)
# {
#   
#   parameter <- rbind(chain@parameter, chain@hyperParameter)
#   
#   values <- paste("(",
#                   paste( 	chain@db2id,
#                           iteration,
#                           ticks(rownames( parameter )),
#                           parameter[,1],
#                           sep=",",collapse='),(' 
#                   ),
#                   ")",sep=""
#   )
#   insert_intoDB("Parameter",values)
#   
# }

